/*
 * 創建期日 2005-5-12
 */
package com.taylor.core.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.taylor.core.db.ParameterProcessor;
import com.taylor.core.db.RowProcessor;
import com.taylor.core.db.RowProcessorForPage;

/**
 * <p>Title: Livedorr</p>
 * <p>Description: Taylor</p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: 英極軟件開發（大連）有限公司</p>
 * @author 凌です
 * @version 1.0
 */

public class DBUtils {
	private DBUtils() {
	}

	/**
	 *  execute Update & Insert & Delete SQL 
	 * 
	 */
	public static int update(Connection conn, String sql) throws SQLException {
		return update(conn, sql, (Object[]) null);
	}

	public static int update(Connection conn, String sql, Object param)
		throws SQLException {
		return update(conn, sql, new Object[] { param });
	}

	public static int update(Connection conn, String sql, Object[] params)
		throws SQLException {

		PreparedStatement stmt = null;
		int rows = 0;

		try {
			stmt = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					stmt.setObject(i + 1, params[i]);
				}
			}
			rows = stmt.executeUpdate();

		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		return rows;
	}

	public static int update(
		Connection conn,
		String sql,
		ParameterProcessor pp)
		throws SQLException {

		PreparedStatement stmt = null;
		int rows = 0;

		try {
			stmt = conn.prepareStatement(sql);
			pp.fillStatement(stmt);
			rows = stmt.executeUpdate();

		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		return rows;
	}
	
	public static int[] batch(
			Connection conn,
			String sql,
			ParameterProcessor pp)
			throws SQLException {
		PreparedStatement stmt = null;
		int rows[];

		try {
			stmt = conn.prepareStatement(sql);
			pp.fillStatement(stmt);
			rows = stmt.executeBatch();

		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		return rows;
	}
	
public static int[] batch(
		Connection conn,
		List sqlAndValueList)
		throws SQLException {
		Statement stmt = null;
		int rows[];
		
		try {
			stmt = conn.createStatement();
			
			if (sqlAndValueList != null && sqlAndValueList.size() != 0) {
				int len = sqlAndValueList.size();
				for (int i = 0; i < len; i++) {
					stmt.addBatch(sqlAndValueList.get(i).toString().trim());
				}					
			}
			
			rows = stmt.executeBatch();
			
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		
		return rows;
	}

/**
 *  execute Query SQL  
 *  return Bean or Bean List
 */
public static Object queryBean(
	Connection conn,
	String sql,
	Object[] params,
	RowProcessor rp)
	throws SQLException {

	PreparedStatement stmt = null;
	ResultSet rs = null;
	try {

		stmt = conn.prepareStatement(sql);
		fillStatement(stmt, params);
		rs = stmt.executeQuery();
		if (rs.next()) {
			return rp.pouplateRow(rs);
		}
		return null;
	} finally {
		if (rs != null)
			rs.close();
		if (stmt != null)
			stmt.close();
	}

}

/**
 * Get List of saving one Column value
 * @param conn
 * @param sql sql statement
 * @param columnName column name
 * @param params where params 
 * @return List of saving one Column value
 * @throws SQLException
 */
public static List queryColumnList(
	Connection conn,
	String sql,
	String columnName,
	Object[] params)
	throws SQLException {

	PreparedStatement stmt = null;
	ResultSet rs = null;
	try {

		stmt = conn.prepareStatement(sql);
		fillStatement(stmt, params);
		rs = stmt.executeQuery();
		List l = new ArrayList();
		while (rs.next()) {
			l.add(rs.getObject(columnName));
		}
		return l;
	} finally {
		if (rs != null)
			rs.close();
		if (stmt != null)
			stmt.close();
	}

}

public static List queryBeanList(
	Connection conn,
	String sql,
	Object[] params,
	RowProcessor rp)
	throws SQLException {

	PreparedStatement stmt = null;
	ResultSet rs = null;
	try {

		stmt = conn.prepareStatement(sql);
		fillStatement(stmt, params);
		rs = stmt.executeQuery();
		List l = new ArrayList();
		while (rs.next()) {
			l.add(rp.pouplateRow(rs));
		}
		return l;
	} finally {
		if (rs != null)
			rs.close();
		if (stmt != null)
			stmt.close();
	}

}

public static List queryBeanList(
	Connection conn,
	String sql,
	Object[] params,
	RowProcessorForPage rp,
	int first)
	throws SQLException {

	PreparedStatement stmt = null;
	ResultSet rs = null;
	try {
        int offset = 0;
		stmt = conn.prepareStatement(sql);
		fillStatement(stmt, params);
		rs = stmt.executeQuery();
		List l = new ArrayList();
		while (rs.next()) {
			offset++;
			l.add(rp.pouplateRow(rs, first, offset));
		}
		return l;
	} finally {
		if (rs != null)
			rs.close();
		if (stmt != null)
			stmt.close();
	}

}

/**
 * execute Page Query SQL
 * 
 */
public static List pageQueryBeanList(
	Connection conn,
	String sql,
	int firstResults,
	int maxResults,
	Object[] params,
	RowProcessor rp)
	throws SQLException {
	String _sql = sql + " LIMIT " + maxResults + " OFFSET " + firstResults;
	return queryBeanList(conn, _sql, params, rp);
}

public static List pageQueryBeanList(
	Connection conn,
	String sql,
	int firstResults,
	int maxResults,
	Object[] params,
	RowProcessorForPage rp)
	throws SQLException {
	String _sql = sql + " LIMIT " + maxResults + " OFFSET " + firstResults;
	return queryBeanList(conn, _sql, params, rp, firstResults);
}

public static void fillStatement(PreparedStatement stmt, Object[] params)
	throws SQLException {

	if (params == null) {
		return;
	}

	for (int i = 0; i < params.length; i++) {
		if (params[i] != null) {
			stmt.setObject(i + 1, params[i]);
		} else {
			stmt.setNull(i + 1, Types.NULL);
		}
	}
}



//wangpeng DAO

/**
 * update operate
 * @param sql the sql of execute update
 * @param obj param of sql,if no parameter is null
 * @return updated row count
 * @throws SQLException
 * @see com.taylor.core.dao.DAO#update(java.lang.String)
 */
public static int update(String sql,Object[] obj,Connection conn) throws SQLException{
	QueryRunner qr = new QueryRunner();
	int count = 0;
		if(obj == null){
			count = qr.update(conn, sql);
        }
		count = qr.update(conn, sql, obj);
		return count;
}

/** 
 * select operate
 * @param sql the sql to be executed selecting,if no parameter is null
 * @param bean the type of resultSet bean 
 *        where the field is equals the Datebase;<br>
 *        <i>eg: xxxBean.class</i>
 * @param obj param of sql 
 * @return resultSet bean list
 * @see com.taylor.core.dao.DAO#select(java.lang.String, java.lang.Class)
 */
public static List select(String sql, Class bean,Object[] obj,Connection conn) throws SQLException{
	QueryRunner qr = new QueryRunner();
	ResultSetHandler rsh = new BeanListHandler(bean);

		if(obj == null){
			return (List)qr.query(conn,sql,rsh);
        }
		return  (List)qr.query(conn,sql,obj,rsh);
}	

/** 
 * execute batch
 * @param sql the sql to be executed batch
 * @param obj batch sql parameter[] 
 * @see com.taylor.core.dao.DAO#batch(java.lang.String, java.lang.Object[][])
 */
public static int[] batch(String sql, Object[][] obj,Connection conn)throws SQLException{
	QueryRunner qr = new QueryRunner();
	int count[]=null;
		count = qr.batch(conn, sql, obj);
		return count;
}


}
